﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.SqlClient;

namespace HTComics.Data.Tools
{
    public class DataImporter
    {
        public static DataTable ImportData(string connString, string filePath, out List<string> errors)
        {
            DataTable dtData = new DataTable();
            DataRow dr;
            string []dataLines = File.ReadAllText(filePath).Split('\n');
            string []record;
            errors = new List<string>();

            Dictionary<string, int> cTypes = GetConditionTypes(connString);

            //Add Columns
            AddColumns(dtData);

            try
            {

                for (int i = 0; i <= dataLines.Length - 2; i++)
                {
                    record = dataLines[i].Split('\t');
                    dr = dtData.NewRow();

                    try
                    {

                        int ctId = -1;
                        if (record[3].Trim() == "G+")
                            ctId = cTypes["GD+"];
                        else if (record[3].Trim() == "G")
                            ctId = cTypes["GD"];
                        else if (record[3].Trim() == "G-")
                            ctId = cTypes["GD-"];
                        else if (record[3].Trim() == "FA")
                            ctId = cTypes["FR"];
                        else
                            ctId = cTypes[record[3].Trim()];


                        dr["ComicTitle"] = record[0].Replace("'", "''");
                        dr["IssueNumber"] = record[1].Replace("'", "''").Replace('/', '-').Replace(':', '-');
                        dr["Quantity"] = record[2].Replace("'", "''");
                        dr["Condition"] = ctId;
                        dr["Cost"] = record[4].Replace("'", "''");
                        dr["CoverPrice"] = record[5].Replace("'", "''");
                        dr["SellingPrice"] = record[6].Replace("'", "''");
                        dr["Notes"] = record[7].Replace("'", "''");
                        dr["Storylines"] = record[8].Replace("'", "''");
                        dr["Writer"] = record[9].Replace("'", "''");
                        dr["Artist"] = record[10].Replace("'", "''");
                        dr["CoverDate"] = record[11].Replace("'", "''");
                        dr["CustomField1"] = record[12].Replace("'", "''");
                        dr["Appearances"] = record[13].Replace("'", "''");
                        dr["Colorists"] = record[14].Replace("'", "''");
                        dr["CoverArtist"] = record[15].Replace("'", "''");
                        dr["ComicInker"] = record[16].Replace("'", "''");
                        dr["Editor"] = record[17].Replace("'", "''");
                        dr["Inker"] = record[18].Replace("'", "''");
                        dr["Letterer"] = record[19].Replace("'", "''");

                        dtData.Rows.Add(dr);
                    }
                    catch (Exception ex)
                    {
                        errors.Add("Line : " + (i + 1) + " Error:" + ex.Message);
                    }
                }
            }
            catch (Exception ex)
            {
                
            }


            return dtData;
        }

        private static void AddColumns(DataTable dt)
        {
            dt.Columns.Add("ComicTitle", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("IssueNumber", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("Quantity", typeof(int)).AllowDBNull = false;
            dt.Columns.Add("Condition", typeof(int)).AllowDBNull = false;
            dt.Columns.Add("Cost", typeof(double)).AllowDBNull = false;
            dt.Columns.Add("CoverPrice", typeof(double)).AllowDBNull = false;
            dt.Columns.Add("SellingPrice", typeof(double)).AllowDBNull = false;
            dt.Columns.Add("Notes", typeof(string));
            dt.Columns.Add("Storylines", typeof(string));
            dt.Columns.Add("Writer", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("Artist", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("CoverDate", typeof(DateTime)).AllowDBNull = false;
            dt.Columns.Add("CustomField1", typeof(string));
            dt.Columns.Add("Appearances", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("Colorists", typeof(string));
            dt.Columns.Add("CoverArtist", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("ComicInker", typeof(string));
            dt.Columns.Add("Editor", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("Inker", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("Letterer", typeof(string));
        }

        private static Dictionary<string, int> GetConditionTypes(string connString)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd;
            Dictionary<string, int> cTypes = new Dictionary<string, int>();
            SqlDataReader reader;

            try
            {
                conn.Open();

                cmd = conn.CreateCommand();
                cmd.CommandText = "SP_GET_CONDITION_TYPES";

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    cTypes.Add(reader["Code"].ToString(), Convert.ToInt32(reader["ConditionId"]));
                }

            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return cTypes;
        }
    }
}
